java如何创建Criteria Builder查询来连接三个具有一对一和多对一实体关系的表？

Question

我想创建一个用于连接三个表的criteria builder查询。我研究过互联网，但所有三个表连接的例子都有不同于我想要的关系。因此，我有三个表具有以下结构：

以及以下实体：

@Entity
@Table(name = "DEPARTMENT")
@Data
public class Department {
    @Id
    @GeneratedValue(generator = "uuid")
    @Column(unique = true, length = 36)
    private String id;

    @NotNull
    @Column(name = "NAME")
    private String name;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "HEAD_ID")
    private Head head;
}

@Entity
@Table(name = "EMPLOYEES")
@Data
public class Employees {
    @Id
    @GeneratedValue(generator = "uuid")
    @Column(unique = true, length = 36)
    private String id;

    @NotNull
    @Column(name = "EMP_NAME")
    private String empName;

    @Column(name = "RESUME")//clob
    private String resume;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "DEPT_ID")
    private Department department;
}

@Entity
@Table(name = "CONTACT")
@Data
public class Contact {
    @Id
    @GeneratedValue(generator = "uuid")
    @Column(unique = true, length = 36)
    private String id;

    @NotNull
    @Column(name = "NUMBER")
    private Long number;

    @NotNull
    @Column(name = "CITY")
    private String city;

    @Column(name = "ADDRESS")//clob
    private String address;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "DEPT_ID")
    private Department department;
}

现在我想

Get the contact of department in which an employee named Mike is currently posted under the head has id=1234.

我尝试了以下问题

CriteriaBuilder builder = getCriteriaBuilder();
CriteriaQuery<Contact> criteria = builder.createQuery(Contact.class);
Root<Contact> contactRoot = criteria.from(Contact.class);
Root<Employees> employeesRoot = criteria.from(Employees.class);
//Join<Employees, Department> employeesDepartmentJoin = contactRoot.join("department", JoinType.INNER); With this statement#: Throws java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [empName] on this ManagedType [org.sample.something.Contact] exception.

Join<Employees, Department> employeesDepartmentJoin = employeesRoot.join("department", JoinType.INNER);
Join<Contact, Department> contactDepartmentJoin = contactRoot.join("department", JoinType.INNER);

List predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(employeesDepartmentJoin.get("head").get("id"), headID));
predicates.add(builder.equal(employeesDepartmentJoin.getParent().get("empName"), empName));
criteria.where(builder.and((Predicate[]) predicates.toArray(new Predicate[0])));
criteria.select(contactRoot).distinct(true);
return getEm().createQuery(criteria).getResultList(); //Without statement#: Throws org.hibernate.exception.SQLGrammarException: could not extract ResultSet exception.

语法异常期间生成的hibernate查询是：

select distinct contact0_.id as id1_47_, contact0_.number as number_id9_47_, contact0_.city as city10_47_, contact0_.address as address10_47_, contact0_.department_id as department11_47_
from sample.contact contact0_ 
inner join sample.department department2_ on contact0_.department_id=department2_.id
cross join sample.employees employees1_ 
inner join sample.department department3_ on employees1_.department_id=department3_.id 
where department3_.head_id=? and employees1_.employee_name=?

任何能照亮它的人都会很有帮助。谢谢

编辑：使用新字段和SQLGrammarException的StackTrace更新了代码（第一个例外是“无法定位属性…”在求值时抛出，否则会抛出相同的SQLGrammarException）

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1514)
    at org.hibernate.query.Query.getResultList(Query.java:132)
    at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:74)
    at org.sample.something.dao.ContactDAO.getContactByHeadAndEmployeeName(ContactDAO.java:102)
    ... 61 common frames omitted
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
    at org.hibernate.loader.Loader.doQuery(Loader.java:937)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
    at org.hibernate.loader.Loader.doList(Loader.java:2689)
    at org.hibernate.loader.Loader.doList(Loader.java:2672)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
    at org.hibernate.loader.Loader.list(Loader.java:2501)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
    ... 64 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)